package com.yyyzzz.DAO.impl;

import com.yyyzzz.DAO.courseDao;
import com.yyyzzz.bean.Course;
import com.yyyzzz.bean.StudentCourse;

import javax.management.DescriptorRead;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class courseDaoimpl implements courseDao {
    Course course = null;
    private Connection conn;
    private Statement stmt;
    private PreparedStatement ps;
    ResultSet rs;

    //增加课程
    @Override
    public int insertCourse(Course course) {
        String driverClass = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/yyyzzz?serverTimezone=UTC&characterEncoding=utf-8";
        String name = "root";
        String pwd = "123456";

        try {
            Class.forName(driverClass);
            conn = DriverManager.getConnection(url, name, pwd);
            String sql = "insert into course(courseid,coursename,courseteacher,classroom,credit) values(?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, course.getCourseId());
            ps.setString(2, course.getCourseName());
            ps.setString(3, course.getCourseTeacher());
            ps.setString(4, course.getClassroom());
            ps.setDouble(5, course.getCredit());
            int row = ps.executeUpdate();
            conn.close();
            ps.close();
            return row;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return 0;
    }

    //查找所有学生信息
    @Override
    public List<Course> selectAll() {
        String driverClass = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/yyyzzz?serverTimezone=UTC&characterEncoding=utf-8";
        String name = "root";
        String pwd = "123456";

        try {
            Class.forName(driverClass); //加载驱动
            conn = DriverManager.getConnection(url, name, pwd);//配置
            String sql = "select * from course";

            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();

            List<Course> courseList = new ArrayList<>();
            while (rs.next()) {
                Course course = new Course();
                course.setCourseId(rs.getString("courseid"));
                course.setCourseName(rs.getString("coursename"));
                course.setCourseTeacher(rs.getString("courseteacher"));
                course.setClassroom(rs.getString("classroom"));
                course.setCredit(rs.getDouble("credit"));
                courseList.add(course);
            }
            for (Course course : courseList) {
                System.out.println(course);
            }

            ps.close();
            conn.close();
            return courseList;
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    }

    //修改课程
    @Override
    public int updateCourse(Course course) {
        String driverClass = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/yyyzzz?serverTimezone=UTC&characterEncoding=utf-8";
        String name = "root";
        String pwd = "123456";

        try {
            Class.forName(driverClass);
            conn = DriverManager.getConnection(url, name, pwd);
            String sql = "update course set courseid=?,coursename=?,courseteacher=?,classroom=?,credit=? where courseid='" + course.getCourseId() + "'";
            ps = conn.prepareStatement(sql);
            ps.setString(1, course.getCourseId());
            ps.setString(2, course.getCourseName());
            ps.setString(3, course.getCourseTeacher());
            ps.setString(4, course.getClassroom());
            ps.setDouble(5, course.getCredit());
            int row = ps.executeUpdate();
            conn.close();
            ps.close();
            return row;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return 0;
    }

    //根据课程号查询课程
    @Override
    public List selectOne(String courseId) {
        String driverClass = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/yyyzzz?serverTimezone=UTC&characterEncoding=utf-8";
        String name = "root";
        String pwd = "123456";


        try {
            Class.forName(driverClass);
            conn = DriverManager.getConnection(url, name, pwd);
            String sql = "select * from course where courseid=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, courseId);
            rs = ps.executeQuery();
            List<Course> courseList = new ArrayList<>();

            if (rs.next()) {
                Course course = new Course();
                course.setCourseId(rs.getString("courseid"));
                course.setCourseName(rs.getString("coursename"));
                course.setCourseTeacher(rs.getString("courseteacher"));
                course.setClassroom(rs.getString("classroom"));
                course.setCredit(rs.getDouble("credit"));
                courseList.add(course);

            }

            conn.close();
            ps.close();
            return courseList;

        } catch (SQLException throwables) {

            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }


        return null;
    }

    @Override
    public int deleteCourseByid(String courseid) {
        String driverClass = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/yyyzzz?serverTimezone=UTC&characterEncoding=utf-8";
        String name = "root";
        String pwd = "123456";

        try {
            Class.forName(driverClass);
            conn = DriverManager.getConnection(url, name, pwd);
            String sql="delete from course where courseid='"+courseid+"'";
            ps=conn.prepareStatement(sql);
            int row=ps.executeUpdate();
            conn.close();
            ps.close();
            return row;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<StudentCourse> getAllStudentCourse() {
        String driverClass = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/yyyzzz?serverTimezone=UTC&characterEncoding=utf-8";
        String name = "root";
        String pwd = "123456";

        try {
            Class.forName(driverClass);
            conn= DriverManager.getConnection(url,name,pwd);
            String sql="select * from studentcourse";
            ps= conn.prepareStatement(sql);
            rs = ps.executeQuery();

            List<StudentCourse> studentCourseList = new ArrayList<>();
            while (rs.next()) {
                StudentCourse studentCourse = new StudentCourse();
                studentCourse.setCourseId(rs.getString("courseid"));
                studentCourse.setCourseName(rs.getString("coursename"));
                studentCourse.setCourseTeacher(rs.getString("courseteacher"));
                studentCourse.setClassroom(rs.getString("classroom"));
                studentCourse.setCredit(rs.getDouble("credit"));
                studentCourseList.add(studentCourse);
            }
            for (StudentCourse studentCourse : studentCourseList) {
                System.out.println(studentCourse);
            }

            ps.close();
            conn.close();
            return studentCourseList;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

}
